package top.lishuoboy.jdbc.a3crud;

import lombok.extern.slf4j.Slf4j;
import top.lishuoboy.jdbc.a2conn.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * 实现普通的 增删改查
 *
 * @author lishuoboy
 * @date 2021-12-19 18:49
 */
@Slf4j
public class A3crud {

    public static void main(String[] args) {
        Connection conn = null;
        Statement st1 = null;
        PreparedStatement ps2 = null;
        PreparedStatement ps3 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        try {
            // 创建连接
            conn = JDBCUtil.getConn();

            // 3.1 执行增删改SQL
            // 方法一 Statement（问题一：存在拼串操作，繁琐。问题二：存在SQL注入问题）：
            String sSql1 = "insert into class_innodb (classid,classname,remark) values (180,'180班','方法一')";
            log.debug("sSql1:" + sSql1);
            st1 = conn.createStatement();
            int successCount1 = st1.executeUpdate(sSql1);
            log.debug("successCount1:" + successCount1);

            // 方法二 PreparedStatement：
            String sSql2 = "insert into class_innodb (classid,classname,remark) values (180,'180班','方法二')";
            log.debug("sSql2:" + sSql2);
            ps2 = conn.prepareStatement(sSql2);
            int successCount2 = ps2.executeUpdate(sSql2);
            log.debug("successCount2:" + successCount2);

            // 方法三 PreparedStatement(推荐)：
            String sSql3 = "insert into class_innodb (classid,classname,remark) values (?,?,?)";
            log.debug("sSql3:" + sSql3);
            ps3 = conn.prepareStatement(sSql3);
            ps3.setInt(1, 180);
            ps3.setString(2, "180班");
            ps3.setString(3, "方法三");
            log.debug("ps3.toString():" + ps3.toString());
            int successCount3 = ps3.executeUpdate();
            log.debug("successCount3:" + successCount3);

            // 3.2 执行查询SQL
            // 方法一 Statement：
            sSql1 = "select * from class_innodb where remark ='方法一'  ";
            log.debug("sSql1:" + sSql1);
            rs1 = st1.executeQuery(sSql1);
            while (rs1.next()) {
                System.out.print("\t" + rs1.getString("classid"));
                System.out.print("\t" + rs1.getString("classname"));
                System.out.print("\t" + rs1.getString("remark"));
                System.out.println();
                System.out.print("\t" + rs1.getString(1));
                System.out.print("\t" + rs1.getString(2));
                System.out.print("\t" + rs1.getString(3));
                System.out.println();
            }

            // 方法二 PreparedStatement：
            sSql2 = "select * from class_innodb where remark ='方法二'  ";
            log.debug("sSql2:" + sSql2);
            rs2 = ps2.executeQuery(sSql2);
            while (rs2.next()) {
                System.out.print("\t" + rs2.getString("classid"));
                System.out.print("\t" + rs2.getString("classname"));
                System.out.print("\t" + rs2.getString("remark"));
                System.out.println();
                // System.out.print("\t" + rs2.getString(1));
                // System.out.print("\t" + rs2.getString(2));
                // System.out.print("\t" + rs2.getString(3));
                // System.out.println();;
            }

            // 方法三 PreparedStatement：
            sSql3 = "select * from class_innodb where remark =? ";
            log.debug("sSql3:" + sSql3);
            ps3 = conn.prepareStatement(sSql3);
            ps3.setString(1, "方法三");
            log.debug("ps3.toString():" + ps3.toString());
            rs3 = ps3.executeQuery();
            while (rs3.next()) {
                System.out.print("\t" + rs3.getString("classid"));
                System.out.print("\t" + rs3.getString("classname"));
                System.out.print("\t" + rs3.getString("remark"));
                System.out.println();
                // System.out.print("\t" + rs3.getString(1));
                // System.out.print("\t" + rs3.getString(2));
                // System.out.print("\t" + rs3.getString(3));
                // log.debug();
            }
        } catch (Exception e) {
            log.error("异常", e);
        } finally {
            JDBCUtil.close(rs1, rs2, rs3, st1, ps2, ps3, conn);
        }
    }
}
